package com.telsoft.hms.lib.data;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.slf4j.*;

import com.telsoft.hms.entity.ETT_DBInfo;

import snaq.db.ConnectionPool;
import snaq.db.ConnectionPoolManager;

public class DBPool {
	protected Connection conn;
	protected ConnectionPoolManager connManager;
	private static DBPool dbPool;
	private static Logger logger = LoggerFactory.getLogger(DBPool.class);
	static String databaseName = null;
	static String fileProperty = "dbpool.properties";
	private Map<String, ConnectionPool> poolList = new ConcurrentHashMap<String, ConnectionPool>();

	public DBPool() {
		try {
			// if (dbPool == null) {
			connManager = ConnectionPoolManager.getInstance(fileProperty);
			// }
		} catch (IOException ex) {
			logger.info("Error While Connecting with DBPool Properties file :=> "
					+ ex.toString());
		}
	}

	public DBPool(String strPoolName) {
		databaseName = strPoolName;
		try {
			// if (dbPool == null) {
			connManager = ConnectionPoolManager.getInstance(fileProperty);
			// }
		} catch (IOException ex) {
			logger.info("Error While Connecting with DBPool Properties file :=> "
					+ ex.toString());
		}
	}

	public static DBPool getInstance(String strPoolName) {
		databaseName = strPoolName;
		if (dbPool == null)
			dbPool = new DBPool();
		return dbPool;
	}

	public Connection getConn(String strPoolName) throws SQLException {
		Connection con = null;
		try {
			con = connManager.getConnection(strPoolName);
			logger.info("Connection Created: " + con.toString());
		} catch (SQLException ex) {
			logger.info("Error While Creating Connection :=> " + ex.toString());
			throw ex;
		}
		if (con != null) {
			this.conn = con;
			logger.info("Connection Released: " + this.conn.toString());
			return con;
		}
		return con;
	}

	public ETT_DBInfo getParameterConnection(String key) throws Exception {
		ETT_DBInfo ettDBInfo = new ETT_DBInfo();
		String strSQL = "SELECT a.code,"
				+ " b.driver_class_name, b.url, b.user_name, "
				+ " b.password, b.min_pool, b.max_pool, "
				+ " b.max_size, b.idle_timeout, b.status "
				+ " FROM remap_schema a,  remap_db_info b "
				+ " WHERE a.remap_schema_id = b.remap_schema_id AND a.code = ?";
		Connection con = getConn(databaseName);
		PreparedStatement stm = null;
		ResultSet rs = null;
		try {
			stm = con.prepareStatement(strSQL);
			stm.setString(1, key);
			rs = stm.executeQuery();
			if (rs.next()) {
				ettDBInfo.setStrPoolName(rs.getString("code"));
				ettDBInfo.setStrDriverClassName(rs
						.getString("driver_class_name"));
				ettDBInfo.setStrUrl(rs.getString("url"));
				ettDBInfo.setStrUserName(rs.getString("user_name"));
				ettDBInfo.setStrPassword(rs.getString("password"));
				ettDBInfo.setiMinPool(rs.getInt("min_pool"));
				ettDBInfo.setiMaxPool(rs.getInt("max_pool"));
				ettDBInfo.setiMaxSize(rs.getInt("max_size"));
				ettDBInfo.setiIdleTimeout(rs.getInt("idle_timeout"));
				ettDBInfo.setStrStatus(rs.getString("status"));
			} else {
				throw new Exception("POOL-0001");
			}
			return ettDBInfo;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.info(e.getMessage());
			throw e;
		} finally {
			rs.close();
			stm.close();
			con.close();
		}
		// return null;
	}

	public int getMaxSessionLoginDb(String strCode) throws SQLException {
		String strSQL = "SELECT a.value FROM hms_restriction a, remap_db_info b, remap_schema c"
				+ "WHERE c.remap_schema_id = b.remap_schema_id AND b.remap_schema_id = a.remap_schema_id"
				+ "AND a.TYPE = 'SESSION' AND c.code = ?";
		Connection con = getConn(databaseName);
		PreparedStatement stm = null;
		ResultSet rs = null;
		int iMaxSessionLogin = 0;
		try {
			stm = con.prepareStatement(strSQL);
			stm.setString(1, strCode);
			rs = stm.executeQuery();
			if (rs.next()) {
				iMaxSessionLogin = rs.getInt("value");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.info(e.getMessage());
			throw e;
		} finally {
			rs.close();
			stm.close();
			con.close();
		}
		return iMaxSessionLogin;
	}

	public boolean createConnectionPool(ETT_DBInfo ettDBInfo)
			throws SQLException, Exception {
		// try {
		Driver driver = null;
		try {
			driver = DriverManager.getDriver(ettDBInfo.getStrDriverClassName());
		} catch (SQLException ex) {
			;
		}
		if (driver == null) {
			DriverManager.registerDriver((Driver) Class.forName(
					ettDBInfo.getStrDriverClassName()).newInstance());
		}

		// Check
		ConnectionPool subPool = poolList.get(ettDBInfo.getStrPoolName());
		if (subPool == null) {
			ConnectionPool pool = new ConnectionPool(
					ettDBInfo.getStrPoolName(), ettDBInfo.getiMinPool(),
					ettDBInfo.getiMaxPool(), ettDBInfo.getiMaxSize(),
					ettDBInfo.getiIdleTimeout(), ettDBInfo.getStrUrl(),
					ettDBInfo.getStrUserName(), ettDBInfo.getStrPassword());
			poolList.put(ettDBInfo.getStrPoolName(), pool);

		}
		return true;
		// } catch (ClassNotFoundException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// } catch (InstantiationException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// } catch (IllegalAccessException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// } catch (SQLException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }
		// return false;
	}

	public void connectionPoolManager(String key) throws Exception {
		// step 1: get info db
		ETT_DBInfo ett = null;
		try {
			ett = new ETT_DBInfo(getParameterConnection(key));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		}
		// step 2: create connectionpool
		if (ett.getStrUserName() != null) {
			if (createConnectionPool(ett)) {
				logger.info("Create connection successful: " + key);
			}
		} else {
			logger.info("Info connection not have: " + key);
			throw new Exception("ERR-0009");
		}
	}

	public Connection getConnectionFromPoolManager(String key) throws Exception {
		connectionPoolManager(key);
		ConnectionPool subPool = poolList.get(key);
		Connection con = null;
		try {
			con = subPool.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw e;
		}
		return con;
	}

}
